import re
import copy

# 预定义需要过滤的表及其字段（保持不变）
TABLE_FIELDS_TO_REMOVE = {
    'fa_kuyou_bom': ['createtime', 'updatetime'],
    'fa_kuyou_bom_info': ['createtime', 'updatetime', 'goods_note', 'remark'],
    'fa_kuyou_procedure': ['createtime', 'updatetime'],
    'fa_kuyou_plan': ['goods_name', 'goods_code', 'goods_unit', 'default_unit', 'basic_nmber', 'unit_price',
                      'createtime', 'updatetime'],
    'fa_kuyou_mrp': ['goods_name', 'goods_code', 'goods_unit', 'default_unit', 'basic_nmber', 'unit_price',
                     'mrp_status', 'createtime', 'updatetime'],
    'fa_kuyou_production': ['finish_number', 'basic_nmber', 'unit_price', 'goods_note', 'goods_code', 'goods_unit',
                            'default_unit', 'delivery_time', 'createtime', 'updatetime'],
    'fa_kuyou_production_info': ['remark', 'requisition_number', 'use_number', 'back_number', 'createtime',
                                 'updatetime'],
    'fa_kuyou_pick': ['goods_name', 'goods_code', 'goods_unit', 'default_unit', 'finish_number', 'basic_nmber',
                      'unit_price', 'delivery_time', 'mrp_status', 'createtime', 'updatetime'],
    'fa_kuyou_pick_info': ['type', 'stock_number', 'requisition_number', 'use_number', 'back_number', 'goods_attr',
                           'createtime', 'updatetime'],
    'fa_kuyou_matetial': ['picking_code', 'delivery_time', 'goods_name', 'goods_code', 'goods_unit', 'default_unit',
                          'finish_number', 'basic_nmber', 'unit_price', 'mrp_status', 'working_id', 'createtime',
                          'updatetime'],
    'fa_kuyou_material_info': ['goods_attr', 'stock_number', 'requisition_number', 'use_number', 'back_number',
                               'period', 'createtime', 'updatetime'],
    'fa_kuyou_outsourcing': ['production_code', 'delivery_time', 'goods_name', 'goods_code', 'goods_unit',
                             'default_unit', 'finish_number', 'basic_nmber', 'unit_price', 'createtime', 'updatetime'],
    'fa_kuyou_outsourcing_info': ['stock_number', 'requisition_number', 'use_number', 'back_number', 'goods_attr',
                                  'period', 'createtime', 'updatetime'],
    'fa_kuyou_acceptance': ['production_code', 'outsourcing_code', 'delivery_time', 'goods_name', 'goods_code',
                            'goods_unit', 'default_unit', 'basic_nmber', 'unit_price', 'mrp_status', 'work_time',
                            'finish_time', 'createtime', 'updatetime'],
    'fa_kuyou_acceptance_info': ['ramark', 'allow_stock', 'goods_attr', 'period', 'createtime', 'updatetime'],
    'fa_kuyou_goods_purchase_order': ['admin_id', 'purchase_id', 'sales_order_id', 'returns_order_id', 'purchase_money',
                                      'check_status', 'pay_money', 'account', 'note', 'arrears', 'all_arrears',
                                      'print_num',
                                      'createtime', 'updatetime'],
    'fa_kuyou_goods_purchase_order_info': ['admin_id', 'goods_attr', 'purchase_id', 'sales_order_id',
                                           'returns_order_id',
                                           'purchase_money', 'preparation_id', 'examine_id', 'operation_id', 'status',
                                           'check_status', 'examine_status', 'examine_time', 'pay_money', 'account',
                                           'print_num', 'library_time', 'createtime', 'updatetime'],
    'fa_kuyou_goods_purchase': ['admin_id', 'sales_order_id', 'returns_order_id', 'createtime', 'updatetime'],
    'fa_kuyou_goods_purchase_info': ['admin_id', 'delivery_time', 'goods_attr', 'sales_order_id', 'returns_order_id',
                                     'preparation_id', 'examine_id', 'operation_id', 'status', 'check_status',
                                     'examine_status', 'examine_time', 'pay_money', 'account', 'period', 'print_num',
                                     'createtime', 'updatetime'],
    'fa_kuyou_goods_exchange': ['admin_id', 'delivery_time'],
    'fa_kuyou_goods_exchange_info': ['admin_id', 'delivery_time', 'goods_attr', 'sales_order_id', 'returns_order_id',
                                     'preparation_id', 'examine_id', 'operation_id', 'status', 'check_status',
                                     'examine_status', 'examine_time', 'pay_money', 'retreat_status', 'retreat_money',
                                     'account', 'print_num', 'period', 'createtime', 'updatetime'],
    'fa_kuyou_goods_sales_order': ['admin_id', 'purchase_id', 'sales_order_id', 'returns_order_id', 'pay_money'
                                                                                                    'check_status',
                                   'account', 'note', 'arrears', 'all_arrears', 'print_num',
                                   'createtime', 'updatetime'],
    'fa_kuyou_goods_sales_order_info': ['admin_id', 'delivery_time', 'goods_attr', 'cost_price', 'purchase_id',
                                        'sales_order_id', 'returns_order_id', 'preparation_id', 'examine_id',
                                        'operation_id', 'status', 'check_status', 'examine_status', 'examine_time',
                                        'pay_money', 'account', 'print_num', 'library_time', 'createtime',
                                        'updatetime'],
    'fa_kuyou_goods_sales': ['admin_id', 'order_code', 'order_time', 'delivery_time', 'sales_order_id',
                             'returns_order_id', 'third_id'],
    'fa_kuyou_goods_sales_info': ['admin_id', 'delivery_time', 'goods_attr', 'purchase_order_id', 'sales_order_id',
                                  'returns_order_id', 'purchase_money', 'customers_money', 'preparation_id',
                                  'examine_id', 'operation_id',
                                  'pay_status', 'check_status', 'examine_status', 'examine_time', 'pay_money',
                                  'account', 'print_num', 'period', 'createtime', 'updatetime'],
    'fa_kuyou_goods_sales_exchange': ['admin_id', 'order_code', 'order_time', 'delivery_time', 'sales_order_id',
                                      'pay_money', 'third_id'],
    'fa_kuyou_goods_sales_exchange_info': ['admin_id', 'delivery_time', 'goods_attr', 'sales_order_id',
                                           'returns_order_id', 'purchase_money', 'customers_money', 'preparation_id',
                                           'examine_id', 'operation_id', 'pay_status', 'check_status',
                                           'examine_time', 'pay_money', 'retreat_status', 'retreat_money', 'account',
                                           'print_num', 'period'],
    'fa_kuyou_customers': ['saler', 'receive_account', 'opening_prepayment', 'admin_id', 'third_id', 'createtime',
                           'updatetime'],
    'fa_kuyou_accounts_copewith': ['admin_id', 'cost', 'createtime', 'updatetime'],
    'fa_kuyou_accounts_receivable': ['admin_id', 'cost', 'createtime', 'updatetime'],

    # TODO 添加每个表所需过滤的字段
}


# 根据表名去除无关字段数据
def extract_table_name(sql: str) -> (str, dict):
    """
    增强版SQL表名提取函数，支持反引号、JOIN、子查询等复杂场景

    :param sql: SQL语句
    :return: (主表名, 别名映射字典{别名: 表名})
    """
    # 简化处理：小写化、移除注释和多余空格
    sql_no_comments = re.sub(r'/\*.*?\*/', '', sql, flags=re.DOTALL)  # 移除块注释
    sql_no_comments = re.sub(r'--.*', '', sql_no_comments)  # 移除行注释
    sql_lower = sql_no_comments.lower().strip()

    # 递归移除嵌套子查询
    sql_clean = re.sub(r'\(\s*select\b.*?\bfrom\b.*?\)', '', sql_lower, flags=re.DOTALL | re.IGNORECASE)

    # 查找FROM关键字位置
    from_match = re.search(r'\bfrom\b\s*(.*?)(?=,?\s*(where|group\s+by|order\s+by|limit|having|join\b|\Z))',
                           sql_clean, re.IGNORECASE)
    if not from_match:
        return None, {}

    from_clause = from_match.group(1).strip()

    # 解析表名和别名
    alias_map = {}
    tables = []

    # 处理JOIN的情况
    tables_seen = set()
    for table_match in re.finditer(
            r'(?:join|inner\s+join|left\s+join|right\s+join|cross\s+join)?\s*'
            r'(?:[`"]?([\w_]+)[`"]?\.)?[`"]?([\w_]+)[`"]?\s*(?:as\s+[`"]?(\w+)[`"]?)?',
            from_clause,
            re.IGNORECASE
    ):
        db_alias = table_match.group(1)
        table_name = table_match.group(2)
        table_alias = table_match.group(3) or table_name

        # 只处理实际表名（忽略子查询别名）
        if table_name not in ('select', 'where', 'group', 'order'):
            tables.append(table_name)

            # 主表处理（FROM后的第一个表）
            if len(tables_seen) == 0:
                main_table = table_name

            tables_seen.add(table_name)
            alias_map[table_alias] = table_name
            if db_alias:
                alias_map[db_alias] = table_name

    if not tables:
        return None, {}

    return main_table, alias_map


# 清洗sql语句查询数据
def filter_data(sql: str, data: tuple, columns: list) -> tuple:
    """
    增强版数据过滤方法（支持反引号、JOIN和别名）

    :param sql: SQL语句
    :param data: 查询结果（元组的元组）
    :param columns: 字段名列表
    :return: (过滤后的数据, 被删除字段的索引)
    """
    # 1. 提取主表和别名映射
    try:
        main_table, alias_map = extract_table_name(sql)
    except Exception as e:
        # 提取失败时返回原始数据
        return data, []

    if not main_table:
        return data, []

    # 2. 识别所有需要过滤的表
    tables_to_filter = set()
    fields_to_remove = []

    for table in alias_map.values():
        if table in TABLE_FIELDS_TO_REMOVE:
            tables_to_filter.add(table)
            fields_to_remove.extend(TABLE_FIELDS_TO_REMOVE.get(table, []))

    # 添加主表过滤规则（即使未在JOIN中明确列出）
    if main_table in TABLE_FIELDS_TO_REMOVE and main_table not in tables_to_filter:
        tables_to_filter.add(main_table)
        fields_to_remove.extend(TABLE_FIELDS_TO_REMOVE[main_table])

    # 3. 处理无结果的情况
    if not data or not columns:
        return data, []

    # 4. 识别需要删除的字段索引
    indexes_to_remove = []

    # 创建字段名到原始格式的映射（忽略大小写和反引号）
    col_normalized_to_original = {}
    for i, col in enumerate(columns):
        normalized = re.sub(r'[`"]', '', col).lower()
        col_normalized_to_original[normalized] = (i, col)

    for field in set(fields_to_remove):
        # 检查两种情况：带表别名前缀的字段和无前缀的字段
        for normalized_col, (orig_idx, orig_col) in col_normalized_to_original.items():
            # 情况1：带表别名前缀的字段（如 gsi.createtime）
            if '.' in normalized_col:
                parts = normalized_col.split('.')
                if len(parts) != 2:
                    continue
                alias, col_name = parts
                if alias in alias_map and alias_map[alias] in tables_to_filter:
                    if col_name == field:
                        indexes_to_remove.append(orig_idx)
            # 情况2：无前缀字段（直接字段名如 createtime）
            else:
                # 只有当主表需要过滤时才处理
                if main_table in tables_to_filter and normalized_col == field:
                    indexes_to_remove.append(orig_idx)

    # 去重并按倒序排列索引
    indexes_to_remove = sorted(set(indexes_to_remove), reverse=True)

    # 5. 执行过滤操作
    if not indexes_to_remove:
        return data, []

    filtered_data = []
    for row in data:
        row_list = list(row)
        for idx in indexes_to_remove:
            if idx < len(row_list):
                del row_list[idx]
        filtered_data.append(tuple(row_list))

    return tuple(filtered_data), indexes_to_remove


# 过滤建表语句无关数据
def filter_create_table_sql(original_sql, table_name):
    """
    过滤建表语句，移除指定字段
    :param original_sql: 原始SQL建表语句
    :param table_name: 当前处理的表名（需与remove_fields_dict中的键匹配）
    :param remove_fields_dict: 需要移除的字段配置（格式：{'表名': ['字段1', '字段2']}）
    :return: 过滤后的SQL建表语句
    """
    # 检查表名是否存在需要移除的字段
    if table_name not in TABLE_FIELDS_TO_REMOVE.keys():
        return original_sql

    fields_to_remove = TABLE_FIELDS_TO_REMOVE[table_name]
    if not fields_to_remove:
        return original_sql

    # 分割SQL为行列表
    lines = original_sql.splitlines()

    # 遍历每一行
    for i, line in enumerate(lines):
        stripped_line = line.strip()
        for field in fields_to_remove:
            # 不删除创建时间和更新时间
            if field is 'createtime' or field is 'updatetime':
                continue
            # 检查字段是否在当前行
            if field in stripped_line:
                # 如果字段在当前行，移除整行
                lines[i] = ''
                break

    # 输出列表的字符串
    return ''.join(lines)